"""
Dinky任务服务
专门处理来自Dinky数据库的任务查询
"""

from typing import List, Optional
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.core.dinky_database import get_dinky_async_session, DinkyDatabaseService
from app.schemas.task import DinkyTaskResponse, TaskStatementResponse
from app.core.logger import logger


class DinkyTaskService:
    """Dinky任务服务类"""
    
    async def get_all_tasks(
        self, 
        skip: int = 0, 
        limit: int = 100,
        enabled_only: bool = True
    ) -> List[DinkyTaskResponse]:
        """
        获取所有任务列表
        
        Args:
            skip: 跳过数量
            limit: 限制数量
            enabled_only: 是否只获取启用的任务
            
        Returns:
            任务列表
        """
        try:
            async with get_dinky_async_session() as db:
                # 构建查询SQL
                where_clause = "WHERE enabled = 1" if enabled_only else ""
                query_sql = f"""
                    SELECT id, name, statement, type, enabled, note, create_time, update_time
                    FROM dinky_task
                    {where_clause}
                    ORDER BY id DESC
                    LIMIT :limit OFFSET :skip
                """
                
                result = await db.execute(
                    text(query_sql),
                    {"limit": limit, "skip": skip}
                )
                tasks = result.fetchall()
                
                return [
                    DinkyTaskResponse(
                        id=task.id,
                        name=task.name,
                        statement=task.statement,
                        type=task.type,
                        enabled=bool(task.enabled),
                        note=task.note,
                        create_time=task.create_time,
                        update_time=task.update_time
                    )
                    for task in tasks
                ]
                
        except Exception as e:
            logger.error(f"获取Dinky任务列表失败: {str(e)}")
            return []
    
    async def get_task_by_id(self, task_id: int) -> Optional[DinkyTaskResponse]:
        """
        根据ID获取任务
        
        Args:
            task_id: 任务ID
            
        Returns:
            任务信息
        """
        try:
            async with get_dinky_async_session() as db:
                result = await db.execute(
                    text("""
                        SELECT id, name, statement, type, enabled, note, create_time, update_time
                        FROM dinky_task
                        WHERE id = :task_id
                    """),
                    {"task_id": task_id}
                )
                task = result.first()
                
                if task:
                    return DinkyTaskResponse(
                        id=task.id,
                        name=task.name,
                        statement=task.statement,
                        type=task.type,
                        enabled=bool(task.enabled),
                        note=task.note,
                        create_time=task.create_time,
                        update_time=task.update_time
                    )
                return None
                
        except Exception as e:
            logger.error(f"获取Dinky任务失败: {task_id}, 错误: {str(e)}")
            return None
    
    async def get_task_statement(self, task_id: int) -> Optional[TaskStatementResponse]:
        """
        获取任务的SQL语句
        
        Args:
            task_id: 任务ID
            
        Returns:
            任务SQL语句信息
        """
        try:
            async with get_dinky_async_session() as db:
                result = await db.execute(
                    text("""
                        SELECT id, name, statement
                        FROM dinky_task
                        WHERE id = :task_id
                    """),
                    {"task_id": task_id}
                )
                task = result.first()
                
                if task:
                    statement = task.statement or ""
                    return TaskStatementResponse(
                        task_id=task.id,
                        task_name=task.name,
                        statement=statement,
                        statement_length=len(statement),
                        has_statement=bool(statement.strip())
                    )
                return None
                
        except Exception as e:
            logger.error(f"获取Dinky任务SQL语句失败: {task_id}, 错误: {str(e)}")
            return None
    
    async def get_tasks_with_statements(
        self, 
        skip: int = 0, 
        limit: int = 100
    ) -> List[DinkyTaskResponse]:
        """
        获取有SQL语句的任务列表
        
        Args:
            skip: 跳过数量
            limit: 限制数量
            
        Returns:
            任务列表
        """
        try:
            async with get_dinky_async_session() as db:
                result = await db.execute(
                    text("""
                        SELECT id, name, statement, type, enabled, note, create_time, update_time
                        FROM dinky_task
                        WHERE statement IS NOT NULL
                        AND statement != ''
                        AND enabled = 1
                        ORDER BY id DESC
                        LIMIT :limit OFFSET :skip
                    """),
                    {"limit": limit, "skip": skip}
                )
                tasks = result.fetchall()
                
                return [
                    DinkyTaskResponse(
                        id=task.id,
                        name=task.name,
                        statement=task.statement,
                        type=task.type,
                        enabled=bool(task.enabled),
                        note=task.note,
                        create_time=task.create_time,
                        update_time=task.update_time
                    )
                    for task in tasks
                ]
                
        except Exception as e:
            logger.error(f"获取有SQL语句的Dinky任务列表失败: {str(e)}")
            return []
    
    async def count_tasks(self, enabled_only: bool = True) -> int:
        """
        统计任务数量
        
        Args:
            enabled_only: 是否只统计启用的任务
            
        Returns:
            任务数量
        """
        try:
            async with get_dinky_async_session() as db:
                where_clause = "WHERE enabled = 1" if enabled_only else ""
                result = await db.execute(
                    text(f"SELECT COUNT(*) FROM dinky_task {where_clause}")
                )
                return result.scalar() or 0
                
        except Exception as e:
            logger.error(f"统计Dinky任务数量失败: {str(e)}")
            return 0
    
    async def count_tasks_with_statements(self) -> int:
        """
        统计有SQL语句的任务数量
        
        Returns:
            任务数量
        """
        try:
            async with get_dinky_async_session() as db:
                result = await db.execute(
                    text("""
                        SELECT COUNT(*)
                        FROM dinky_task
                        WHERE statement IS NOT NULL
                        AND statement != ''
                        AND enabled = 1
                    """)
                )
                return result.scalar() or 0
                
        except Exception as e:
            logger.error(f"统计有SQL语句的Dinky任务数量失败: {str(e)}")
            return 0
    
    async def search_tasks(
        self, 
        keyword: str, 
        skip: int = 0, 
        limit: int = 100
    ) -> List[DinkyTaskResponse]:
        """
        搜索任务
        
        Args:
            keyword: 搜索关键词
            skip: 跳过数量
            limit: 限制数量
            
        Returns:
            任务列表
        """
        try:
            async with get_dinky_async_session() as db:
                result = await db.execute(
                    text("""
                        SELECT id, name, statement, type, enabled, note, create_time, update_time
                        FROM dinky_task
                        WHERE name LIKE :keyword
                        AND enabled = 1
                        ORDER BY id DESC
                        LIMIT :limit OFFSET :skip
                    """),
                    {"keyword": f"%{keyword}%", "limit": limit, "skip": skip}
                )
                tasks = result.fetchall()
                
                return [
                    DinkyTaskResponse(
                        id=task.id,
                        name=task.name,
                        statement=task.statement,
                        type=task.type,
                        enabled=bool(task.enabled),
                        note=task.note,
                        create_time=task.create_time,
                        update_time=task.update_time
                    )
                    for task in tasks
                ]
                
        except Exception as e:
            logger.error(f"搜索Dinky任务失败: {keyword}, 错误: {str(e)}")
            return []
